﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using DataLib.DBUtility;
using log4net;
using System.Reflection;
using System.Collections.Generic;

namespace DataLib.DAL
{
    /// <summary>
    /// 数据访问类:InStorage
    /// </summary>
    public partial class InStorage
    {

        ILog log = log4net.LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);

        #region  ExtensionMethod
        /// <summary>
        /// 根据编号获取对象
        /// </summary>
        /// <param name="inStorageNo"></param>
        /// <returns></returns>
        public DataLib.Model.InStorage GetModelByNo(string inStorageNo)
        {
            Model.InStorage model = new Model.InStorage();
            DataTable dt = GetList("wrlno='" + inStorageNo + "'").Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                model = DataRowToModel(dt.Rows[0]);
            }
            return model;
        }


        /// <summary>
        /// 获取入库单维护数据集
        /// </summary>
        /// <returns></returns>
        public DataTable GetInMangeList(string city)
        {
            DataTable dt = new DataTable();
            SqlParameter[] sqlparameter ={
                new SqlParameter("@Gracphic",SqlDbType.VarChar,10)
             };
            sqlparameter[0].Value = city;
            SqlDataReader dr = DbHelperSQL.RunProcedure("InstoraegeMaintenanceList", sqlparameter);
            dt.Load(dr);
            dr.Close();
            return dt;
        }

        /// <summary>
        /// 获取待提交的入库单的明细重
        /// </summary>
        /// <param name="instorid"></param>
        /// <returns></returns>
        public decimal GetWaitInWeight(int instorid)
        {
            string strsql = "Select Sum(nwt) from barcode where status=-1 and InStrorageID=" + instorid.ToString();
            DataTable dt = DbHelperSQL.Query(strsql).Tables[0];
            decimal dweight = 0;
            if (dt != null && dt.Rows.Count > 0 && dt.Rows[0][0].ToString() != "")
                dweight = Convert.ToDecimal(dt.Rows[0][0].ToString());
            return dweight;

        }

        /// <summary>
        /// 联合查询，返回入库查询的所有明细项
        /// </summary>
        /// <param name="cumer">客户编码或者客户名称 只值客户编码和名称</param>
        /// <param name="strwhre">表名带条件查询，字符串类型注意加''  Instorage ins；Barcode bar；unProcessed un </param>
        /// <param name="intype">--入库方式, 2分类入库  1拆解入库 3生产入库, -1 临时库入库 0 是不选择入库方式</param>
        /// <returns></returns>
        public DataTable getDtByUnionWhere(string cumer, string strwhre, string intype)
        {
            DataTable dt = new DataTable();
            SqlParameter[] sqlparameter ={
                new SqlParameter("@cumwhere",SqlDbType.VarChar,50),
                new SqlParameter("@strWhere",SqlDbType.VarChar,255),
                new SqlParameter("@InType",SqlDbType.VarChar,2)
             };
            sqlparameter[0].Value = cumer;
            sqlparameter[1].Value = strwhre;
            sqlparameter[2].Value = intype;
            SqlDataReader dr = DbHelperSQL.RunProcedure("Ewts_GetInStorageItem", sqlparameter);
            dt.Load(dr);
            dr.Close();
            return dt;
        }

        /// <summary>
        /// 获取待入库列表，从出库单过来
        /// </summary>
        /// <param name="belongcity">归属地</param>
        /// <param name="grphiccity">处理地</param>
        /// <returns></returns>
        public DataTable getWaitInStorageList(string belongcity, string graphiccity)
        {
            DataTable dt = new DataTable();
            SqlParameter[] sqlparameter ={
                new SqlParameter("@BelongCity",SqlDbType.VarChar,20),
                new SqlParameter("@GraphicCity",SqlDbType.VarChar,20)
              
             };
            sqlparameter[0].Value = belongcity;
            sqlparameter[1].Value = graphiccity;

            SqlDataReader dr = DbHelperSQL.RunProcedure("EWMS_GetWaitInSList", sqlparameter);
            dt.Load(dr);
            dr.Close();
            return dt;
        }


        /// <summary>
        /// 2016年7月29日 21:30:49
        /// Redplum
        /// 待入库分页
        /// </summary>
        /// <param name="bcity"></param>
        /// <param name="gcity"></param>
        /// <param name="currpage"></param>
        /// <param name="length"></param>
        /// <param name="totalRecord"></param>
        /// <param name="search"></param>
        /// <returns></returns>
        public DataTable GetWatiInsByStore(int currpage, int length, out int totalRecord, string search, string bcity, string gcity, string order_column, string order_dir)
        {
            totalRecord = 0;
            DataTable dt = new DataTable();
            SqlParameter[] sqlparameter ={
                new SqlParameter("@currpage",SqlDbType.Int),
                new SqlParameter("@Length",SqlDbType.Int),
                new SqlParameter("@totalcount",SqlDbType.Int),
                new SqlParameter("@search",SqlDbType.VarChar,100),
                new SqlParameter("@bcity",SqlDbType.VarChar,50),
                new SqlParameter("@gcity",SqlDbType.VarChar,50),
                new SqlParameter("@order_column",SqlDbType.VarChar,20),
                new SqlParameter("@order_dir",SqlDbType.VarChar,20)

             };
            sqlparameter[0].Value = currpage;
            sqlparameter[1].Value = length;
            sqlparameter[2].Value = totalRecord;
            sqlparameter[2].Direction = ParameterDirection.Output;
            sqlparameter[3].Value = search;
            sqlparameter[4].Value = bcity;
            sqlparameter[5].Value = gcity;
            sqlparameter[6].Value = order_column;
            sqlparameter[7].Value = order_dir;
            SqlDataReader dr = DbHelperSQL.RunProcedure("EWMS_GetWaitInSListByStore", sqlparameter);
            dt.Load(dr);
            totalRecord = Convert.ToInt32(sqlparameter[2].Value);
            dr.Close();
            return dt;

        }

        #region 取消入库提交的存储过程
        /// <summary>
        /// 入库提交,存储过程
        /// </summary>
        /// <param name="instorageid">入库ID</param>
        /// <param name="instype">--1拆解入库 2分类入库  3生产入库 5是移库入库 0 称重入库</param>
        /// <returns></returns>
        //public int InstorageSubmit(int instorageid, string instype,string month)
        //{
        //    DataTable dt = new DataTable();
        //    SqlParameter[] sqlparameter ={
        //        new SqlParameter("@insid",SqlDbType.Int),
        //        new SqlParameter("@instype",SqlDbType.VarChar,2),
        //        new SqlParameter("@month",SqlDbType.VarChar,6)

        //     };
        //    sqlparameter[0].Value = instorageid;
        //    sqlparameter[1].Value = instype;
        //    sqlparameter[2].Value = month;
        //    int rowscount = 0;
        //    int rows = DbHelperSQL.RunProcedure("EWMS_InstorageSubmit", sqlparameter, out rowscount);
        //    return rowscount;
        //}
        #endregion

        ///// <summary>
        ///// 生成条码验证,存储过程
        ///// </summary>
        ///// <param name="bar">BarCode</param>
        ///// <param name="ins">入库单</param>
        ///// <param name="instype">--1拆解入库 2分类入库  3生产入库 5是移库入库 0 称重入库</param>
        ///// <returns></returns>
        //public int InstorageConfirm(Model.BarCode bar, Model.InStorage ins, int insType)
        //{
        //    DataTable dt = new DataTable();
        //    SqlParameter[] sqlparameter ={
        //         new SqlParameter("@loadnumid",SqlDbType.Int)
        //        ,new SqlParameter("@cjoid",SqlDbType.Int)
        //        ,new SqlParameter("@ParmentID",SqlDbType.Int)
        //        ,new SqlParameter("@loadnumv",SqlDbType.NVarChar,12)
        //        ,new SqlParameter("@barcodevalue",SqlDbType.NVarChar,35)
        //        ,new SqlParameter("@goodscode",SqlDbType.NVarChar,15)
        //        ,new SqlParameter("@goodsname",SqlDbType.NVarChar,15)
        //        ,new SqlParameter("@remark",SqlDbType.NVarChar,15)//
        //        ,new SqlParameter("@loadnumv",SqlDbType.NVarChar,12)
        //        ,new SqlParameter("@barcodevalue",SqlDbType.NVarChar,35)
        //        ,new SqlParameter("@goodscode",SqlDbType.NVarChar,10)
        //        ,new SqlParameter("@cjoid",SqlDbType.Int)
        //        ,new SqlParameter("@ParmentID",SqlDbType.Int)
        //        ,new SqlParameter("@loadnumv",SqlDbType.NVarChar,12)
        //        ,new SqlParameter("@barcodevalue",SqlDbType.NVarChar,35)
        //        ,new SqlParameter("@goodscode",SqlDbType.NVarChar,10)
        //        ,new SqlParameter("@cjoid",SqlDbType.Int)
        //        ,new SqlParameter("@ParmentID",SqlDbType.Int)
        //        ,new SqlParameter("@loadnumv",SqlDbType.NVarChar,12)
        //        ,new SqlParameter("@barcodevalue",SqlDbType.NVarChar,35)
        //        ,new SqlParameter("@goodscode",SqlDbType.NVarChar,10)
        //        ,new SqlParameter("@cjoid",SqlDbType.Int)
        //        ,new SqlParameter("@ParmentID",SqlDbType.Int)
        //        ,new SqlParameter("@loadnumv",SqlDbType.NVarChar,12)
        //        ,new SqlParameter("@barcodevalue",SqlDbType.NVarChar,35)
        //        ,new SqlParameter("@goodscode",SqlDbType.NVarChar,10)
        //     };
        //    sqlparameter[0].Value = bar.loadnumid;
        //    sqlparameter[1].Value = bar.cjoid;
        //    int rowscount = 0;
        //    int rows = DbHelperSQL.RunProcedure("EWMS_BarCode_Insert", sqlparameter, out rowscount);
        //    return rowscount;
        //}

        /// <summary>
        /// 入库查询
        /// </summary>
        /// <param name="LoadNum">Load号</param>
        /// <param name="cum">客户</param>
        /// <param name="datetime1">入库日期起</param>
        /// <param name="datetime2">入库日期止</param>
        /// <param name="intye">入库方式</param>
        /// <param name="un_goodscode">处理前物品编码</param>
        /// <param name="bar_goodscode">处理后物品编码</param>
        /// <param name="un_ldh">处理前联单号</param>
        /// <param name="wrlno">入库单号</param>
        /// <param name="storageno">库位</param>
        /// <param name="bar_value">条码</param>
        /// <param name="boxid">箱号</param>
        /// <param name="belongcity">归属地</param>
        /// <param name="graphiccity">物理地</param>
        /// <returns></returns>
        //public DataTable getInStorageItem(string LoadNum, string cum, string datetime1, string datetime2,
        //                    string intye, string un_goodscode, string bar_goodscode,
        //                    string un_ldh, string wrlno, string storageno, string bar_value, string boxid, string belongcity,
        //                    string graphiccity)
        //{
        //    DataTable dt = new DataTable();
        //    SqlParameter[] sqlparameter ={
        //        new SqlParameter("@LoadNum",SqlDbType.VarChar,20),
        //        new SqlParameter("@cum",SqlDbType.VarChar,100),
        //        new SqlParameter("@insDateStart",SqlDbType.DateTime),
        //        new SqlParameter("@insDateEnd",SqlDbType.DateTime),
        //       new SqlParameter("@InType",SqlDbType.VarChar,5),
        //        new SqlParameter("@goodsCodeun",SqlDbType.VarChar,25),
        //       new SqlParameter("@goodsCodebar",SqlDbType.VarChar,25),
        //        new SqlParameter("@ldhun",SqlDbType.VarChar,15),
        //       new SqlParameter("@wrlno",SqlDbType.VarChar,20),
        //        new SqlParameter("@storageno",SqlDbType.VarChar,20),
        //       new SqlParameter("@barvalue",SqlDbType.VarChar,35),
        //        new SqlParameter("@boxid",SqlDbType.VarChar,10),
        //        new SqlParameter("@BelongCity",SqlDbType.VarChar,15),
        //        new SqlParameter("@GraphicCity",SqlDbType.VarChar,15)

        //     };
        //    sqlparameter[0].Value = LoadNum;
        //    sqlparameter[1].Value = cum;
        //    if (datetime1.Trim() == "" || datetime2.Trim() == "")
        //    {
        //    }
        //    else
        //    {
        //        sqlparameter[2].Value = datetime1;
        //        sqlparameter[3].Value = datetime2;
        //    }
        //    sqlparameter[4].Value = intye;
        //    sqlparameter[5].Value = un_goodscode;
        //    sqlparameter[6].Value = bar_goodscode;
        //    sqlparameter[7].Value = un_ldh;
        //    sqlparameter[8].Value = wrlno;
        //    sqlparameter[9].Value = storageno;
        //    sqlparameter[10].Value = bar_value;
        //    sqlparameter[11].Value = boxid;
        //    sqlparameter[12].Value = belongcity;
        //    sqlparameter[13].Value = graphiccity;

        //    SqlDataReader dr = DbHelperSQL.RunProcedure("EWMS_GETInstorageItems", sqlparameter);
        //    dt.Load(dr);
        //    dr.Close();
        //    return dt;
        //}

        /// <summary>
        /// 获取指定入库单的BarCode明细
        /// </summary>
        /// <param name="wrlid"></param>
        /// <param name="indate"></param>
        /// <returns></returns>
        public DataTable GetWrlItems(string wrlid, string indate)
        {

            //先获取符合条件的BarCode 
            DataSet ds = new DataSet();
            StringBuilder sql = new StringBuilder();
            sql.Append("select barcodevalue");
            sql.Append(" from barcode");
            sql.Append(" group by barcodevalue,InStrorageID");
            if (indate != null && indate != "")
            {
                sql.Append(",CONVERT(varchar, createdate, 110) ");
            }
            sql.Append(" having SUM(nwt)<>0 and InStrorageID=");
            sql.Append(wrlid);
            if (indate != null && indate != "")
            {
                sql.Append(" and DATEDIFF(day,CONVERT(varchar, createdate, 110),CONVERT(varchar, '");
                sql.Append(indate);
                sql.Append("', 110))=0");
            }
            //string strsql = @"select barcodevalue  FROM BarCode  where InStrorageID=";
            //strsql += wrlid;
            DataTable dt_bar = new DataTable();
            //if (indate != "" && indate != null)
            //{
            //    DateTime dtime1 = DateTime.Parse(indate);
            //    strsql += " and createdate between '" + indate + "' and '" + dtime1.AddDays(1).AddSeconds(-1).ToString() + "'";
            //}
            //strsql += " group by barcodevalue";
            dt_bar = DbHelperSQL.Query(sql.ToString()).Tables[0];
            string barcodevalue = "";
            DataTable dt = new DataTable();
            for (int i = 0; i < dt_bar.Rows.Count; i++)
            {
                barcodevalue = dt_bar.Rows[i][0].ToString();
                SqlParameter[] sqlparameter ={
                new SqlParameter("@barcode",SqlDbType.VarChar,35) };
                sqlparameter[0].Value = barcodevalue;
                SqlDataReader dr = DbHelperSQL.RunProcedure("Ewts_GetBarCode", sqlparameter);
                dt.Load(dr);
                dr.Close();
            }


            return dt;
        }


        /// <summary>
        /// 根据入库单号查找
        /// </summary>
        ///<param name="datetime1">起</param>
        ///<param name="datetime2">止</param>
        ///<param name="wrlno">入库单号</param>
        /// <returns></returns>
        public DataTable GETItemsByInsNo(string datetime1, string datetime2, string wrlno)
        {
            DataTable dt = new DataTable();
            SqlParameter[] sqlparameter ={
                new SqlParameter("@barDateStart",SqlDbType.DateTime),
                new SqlParameter("@barDateEnd",SqlDbType.DateTime),
                new SqlParameter("@insNo",SqlDbType.VarChar,20)
             };
            if (datetime1.Trim() == "" || datetime2.Trim() == "")
            {
            }
            else
            {
                sqlparameter[0].Value = datetime1;
                sqlparameter[1].Value = datetime2;
            }
            sqlparameter[2].Value = wrlno;


            SqlDataReader dr = DbHelperSQL.RunProcedure("EWMS_GETItemsByInsNo", sqlparameter);
            dt.Load(dr);
            dr.Close();
            return dt;
        }

        /// <summary>
        /// 根据入库单号查找
        /// </summary>
        /// <returns></returns>
        public DataTable GETWRLItem(string loadnum,
                            string CJO,
                            string cum,
                            string peculiar,
                            string collman,
                            string dtime1,
                            string dtime2,
                            string wrlno,
                            string goodscode,
                            string boxid,
                            string barcon,
                            string isLocd,
                            string plateno
                            )
        {
            DataTable dt = new DataTable();
            SqlParameter[] sqlparameter ={
                new SqlParameter("@LoadNum",SqlDbType.VarChar,15),
                new SqlParameter("@CJO",SqlDbType.VarChar,15),
                new SqlParameter("@cum",SqlDbType.VarChar,50),
                new SqlParameter("@collman",SqlDbType.VarChar,15),
                new SqlParameter("@insDateStart",SqlDbType.DateTime),
                new SqlParameter("@insDateEnd",SqlDbType.DateTime),
                new SqlParameter("@wrlno",SqlDbType.VarChar,20),
                new SqlParameter("@goodsCodebar",SqlDbType.VarChar,15),
                new SqlParameter("@boxid",SqlDbType.VarChar,15),
                new SqlParameter("@barcon",SqlDbType.VarChar,25),
                new SqlParameter("@isLocd",SqlDbType.VarChar,2),
                new SqlParameter("@plateno",SqlDbType.VarChar,20),
                new SqlParameter("@peculiarrequire",SqlDbType.VarChar,30)
             };
            sqlparameter[0].Value = loadnum;
            sqlparameter[1].Value = CJO;
            sqlparameter[2].Value = cum;
            sqlparameter[3].Value = collman;
            if (dtime1.Trim() == "" || dtime2.Trim() == "")
            {
            }
            else
            {
                sqlparameter[4].Value = dtime1;
                sqlparameter[5].Value = dtime2;
            }
            sqlparameter[6].Value = wrlno;
            sqlparameter[7].Value = goodscode;
            sqlparameter[8].Value = boxid;
            sqlparameter[9].Value = barcon;

            sqlparameter[10].Value = isLocd;
            sqlparameter[11].Value = plateno;
            sqlparameter[12].Value = peculiar;

            SqlDataReader dr = DbHelperSQL.RunProcedure("EWMS_GETWRLItem", sqlparameter);
            dt.Load(dr);
            dr.Close();
            return dt;
        }

        /// <summary>
        /// 客户查询
        /// </summary>
        /// <returns></returns>
        public DataTable GETWRLItem(string loadnum,
                             string cum,
                            string dtime1,      //收集日期起
                            string dtime2,      //收集日期止
                            string goodsName,   //物品名称
                            string barstatus,    //已处理2 未处理1
                            string isLocd     //是否锁定
                             )
        {
            DataTable dt = new DataTable();
            SqlParameter[] sqlparameter ={
                new SqlParameter("@LoadNum",SqlDbType.VarChar,15),
                new SqlParameter("@cum",SqlDbType.VarChar,50),
                new SqlParameter("@CollDateStart",SqlDbType.DateTime),
                new SqlParameter("@CollDateEnd",SqlDbType.DateTime),
                new SqlParameter("@goodsName",SqlDbType.VarChar,25),
                new SqlParameter("@barStatus",SqlDbType.VarChar,2),
                new SqlParameter("@isLocd",SqlDbType.VarChar,2)
             };
            sqlparameter[0].Value = loadnum;
            sqlparameter[1].Value = cum;
            if (dtime1.Trim() == "" || dtime2.Trim() == "")
            {
            }
            else
            {
                sqlparameter[2].Value = dtime1;
                sqlparameter[3].Value = dtime2;
            }
            sqlparameter[4].Value = goodsName;
            sqlparameter[5].Value = barstatus;
            sqlparameter[6].Value = isLocd;

            SqlDataReader dr = DbHelperSQL.RunProcedure("EWMS_GETWRLItem", sqlparameter);
            dt.Load(dr);
            dr.Close();
            return dt;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="insid">入库ID</param>
        /// <param name="weighttype">1是Barcode，2是辅料，3是损耗,4是出库单重量</param>
        /// <returns></returns>
        public decimal GetWeightByInsId(int insid, int weighttype)
        {
            decimal objweight = 0;
            object obj = null;
            string strsql = "";
            switch (weighttype)
            {
                case 1:
                    strsql = "select sum(nwt) from barcode where isdel_out=0 and InStrorageID=" + insid.ToString();
                    break;
                case 2:
                    strsql = "select sum(nwt) from ins_fuliao where instroageid=" + insid.ToString();
                    break;
                case 3:
                    strsql = "select sum(nwt) from ins_sunhao where instroageid=" + insid.ToString();
                    break;
                case 4:
                    strsql = "select top 1 totalweight ";
                    strsql += " from OutStorage outs,instorage ins  ";
                    strsql += " where ins.outid = outs.id and ins.id=" + insid.ToString();
                    break;
                default:
                    break;
            }

            DataTable dt = DbHelperSQL.Query(strsql).Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                obj = dt.Rows[0][0];
            }
            try
            {
                objweight = decimal.Parse(obj.ToString());
            }
            catch
            {
                objweight = 0;
            }
            return objweight;
        }

        /// <summary>
        /// 得到入库单的总重量
        /// </summary>
        /// <param name="insid">入库单ID</param>
        public decimal GetInsTotalWeight(int insid)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  isnull(Sum(nwt),0) from barcode ");
            strSql.Append(" where InStrorageID=@insid");
            SqlParameter[] parameters = {
					new SqlParameter("@insid", SqlDbType.Int,4)
			};
            parameters[0].Value = insid;

            DataLib.Model.InStorage model = new DataLib.Model.InStorage();
            DataTable dt = DbHelperSQL.Query(strSql.ToString(), parameters).Tables[0];
            if (dt != null && dt.Rows.Count > 0 && dt.Rows[0][0] != null)
            {
                return decimal.Parse(dt.Rows[0][0].ToString());
            }
            else
            {
                return 0;
            }
        }


        /// <summary>
        /// 入库单插入或者修改
        /// </summary>
        /// <returns></returns>
        public int Save(Model.InStorage ins)
        {
            SqlParameter[] sqlparameter ={
                new SqlParameter("@cjoid",SqlDbType.Int),
                new SqlParameter("@wrlno",SqlDbType.VarChar,30),
                new SqlParameter("@instoragetime",SqlDbType.DateTime),
                new SqlParameter("@outid",SqlDbType.Int),
               new SqlParameter("@outno",SqlDbType.VarChar,20),
                new SqlParameter("@Operator",SqlDbType.VarChar,20),
               new SqlParameter("@InDepart",SqlDbType.VarChar,20),
                new SqlParameter("@OutDepart",SqlDbType.VarChar,20),
               new SqlParameter("@status",SqlDbType.Int),
                new SqlParameter("@instotal",SqlDbType.Decimal,15),
               new SqlParameter("@loadnumid",SqlDbType.Int),
                new SqlParameter("@mechanerr",SqlDbType.Decimal,15),
                new SqlParameter("@vloadnum",SqlDbType.VarChar,20),
                new SqlParameter("@clearingmonth",SqlDbType.VarChar,6),
                new SqlParameter("@islock",SqlDbType.Bit),
                new SqlParameter("@ErrorMessage",SqlDbType.VarChar,200)
            
             };
            sqlparameter[0].Value = ins.cjoid;
            sqlparameter[1].Value = ins.wrlno;

            sqlparameter[2].Value = ins.instoragetime;
            sqlparameter[3].Value = ins.outid;
            sqlparameter[4].Value = ins.outno;
            sqlparameter[5].Value = ins.Operator;
            sqlparameter[6].Value = ins.InDepart;
            sqlparameter[7].Value = ins.OutDepart;
            sqlparameter[8].Value = ins.status;
            sqlparameter[9].Value = ins.instotal;
            sqlparameter[10].Value = ins.loadnumid;
            sqlparameter[11].Value = ins.mechanerr;
            sqlparameter[12].Value = ins.vloadnum;
            sqlparameter[13].Value = ins.clearingmonth;
            sqlparameter[14].Value = ins.islock;
            sqlparameter[15].Direction = ParameterDirection.Output;
            int insid = DbHelperSQL.RunProcedure("EWMS_InstorageSave", sqlparameter, out insid);

            return insid;
        }

        /// <summary>
        /// 修改入库单的条码的单一入库日期
        /// </summary>
        /// <param name="insid"></param>
        /// <param name="olddate"></param>
        /// <param name="newdate"></param>
        /// <returns></returns>
        public bool updateBarcodDate(string insid, string olddate, string newdate)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update BarCode ");
            strSql.Append("	set createdate=DATEADD(SECOND,DATEPART(SECOND,createdate),DATEADD(MINUTE,DATEPART(MINUTE,createdate),DATEADD(HOUR,DATEPART(HOUR,createdate),@newdate)))");
            strSql.Append("	where InStrorageID=@insid");
            strSql.Append("		and year(createdate)=YEAR(@olddate)");
            strSql.Append("		and month(createdate)=month(@olddate)");
            strSql.Append("		and day(createdate)=day(@olddate);");
            strSql.Append("update InStorage ");
            strSql.Append("	set instoragetime=(Select min(createdate) from barcode ");
            strSql.Append("	where InStrorageID=@insid) where id=@insid");
            SqlParameter[] parameters = {
					new SqlParameter("@insid", SqlDbType.Int,4),
					new SqlParameter("@olddate", SqlDbType.VarChar,20),
					new SqlParameter("@newdate", SqlDbType.VarChar,20)
			};
            parameters[0].Value = insid;
            parameters[1].Value = olddate;
            parameters[2].Value = newdate;

            DataLib.Model.InStorage model = new DataLib.Model.InStorage();
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }



        /// <summary>
        /// 返回入库单ID
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int InsertPDNo(string bcity, string gcity, string operman)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@bcity", SqlDbType.NVarChar,20),
					new SqlParameter("@gcity", SqlDbType.NVarChar,20),
					new SqlParameter("@operman", SqlDbType.VarChar,20)
                                        };

            parameters[0].Value = bcity;
            parameters[1].Value = gcity;
            parameters[2].Value = operman;
            DataTable dt = new DataTable();

            SqlDataReader dr = DbHelperSQL.RunProcedure("EWMS_PDNoAdd", parameters);
            dt.Load(dr);
            dr.Close();

            int insid = 0;
            if (dt != null && dt.Rows.Count > 0)
            {
                try
                {
                    insid = int.Parse(dt.Rows[0][0].ToString());
                }
                catch
                {
                    insid = 0;
                }
            }
            else
            {
                insid = 0;
            }
            return insid;

        }


        /// <summary>
        /// 入库提交
        /// </summary>
        /// <param name="instorageid">入库ID</param>
        /// <param name="instype">--1拆解入库 2分类入库  3生产入库 5是移库入库 0 称重入库</param>
        /// <returns></returns>
        public int InstorageSubmit(int instorageid, string instype, string month)
        {

            DataTable dt = new DataTable();
            StringBuilder t = new StringBuilder(); //查询用的sql
            List<String> sqlList = new List<string>(); ; //事务用的sql
            t.Append("select * from barcode where InStrorageID=" + instorageid.ToString());
            dt = DbHelperSQL.Query(t.ToString()).Tables[0];
            decimal instotal = 0;
            decimal totalnwt = 0;
            Model.InStorage mins = new InStorage().GetModel(instorageid);
            string vload = "";
            if (mins == null)
                return -1;
            Model.Cjo mcjo = null;
            Model.OutStorage mouts = null;
            int outs_status = 0;

            string str = "";

            if (mins.outid == 0 && mins.cjoid > 0)
            {
                //cjo授权处理
                mcjo = new Cjo().GetModel((int)mins.cjoid);
                log.Info("cjo: " + mcjo.id.ToString());
            }
            else
            {
                mouts = new OutStorage().GetModel((int)mins.outid);
                log.Info("outid: " + mouts.id.ToString());
            }

            string stroid = "0";
            int barstatus = 0;
            switch (instype)
            {
                case "2":       //分类入库
                    barstatus = 3;
                    break;
                case "7":       //直销入库
                    barstatus = 13;
                    break;
                case "5":       //移库入库
                    barstatus = 9;
                    break;
                case "3":       //生产入库
                    barstatus = 7;
                    break;
                case "1":       //拆解入库  6盘库入库另
                    barstatus = 5;
                    break;
                case "0":       //称重入库
                    barstatus = 1;
                    break;
                case "6":
                    barstatus = 11;
                    break;      //盘库入库
                default:
                    break;

            }

            if (dt != null && dt.Rows.Count > 0)
            {
                Model.BarCode mbar = new Model.BarCode();
                BarCode dbar = new BarCode();
                log.Info("datatable rows:" + dt.Rows.Count.ToString());
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    mbar = dbar.DataRowToModel(dt.Rows[i]);
                    instotal += (decimal)mbar.nwt;              //入库单重量
                    if (mbar.status == -1)
                    {
                        //插入库存表数据
                        log.Info("barcodeid:" + mbar.id.ToString());
                        if (mbar.OutStrorageID != null)
                            stroid = mbar.OutStrorageID.ToString();
                        else
                            stroid = "0";
                        str = " insert into StorageData(barcodeid,storageid,isused,startNwt,endNwt,nwt,storageno,InOrOut,goodsid,goodscode,goodsName,inoutid,bardate,operDate,operman,instorageid,outstoageid,belongcity,graphiccity,src_bcity,src_gcity,insnwt,outsnwt)";
                        str += " values(" + mbar.id.ToString() + "," + mbar.storageid.ToString() + "," + "0,0,0,";//期初期末值另表，默认为0
                        str += mbar.nwt.ToString() + ",'" + mbar.stroageNo + "',1," + mbar.goodsid.ToString() + ",'" + mbar.goodscode + "','" + mbar.goodsname.Replace("'", "\"") + "'," + mbar.InStrorageID.ToString() + ",'";
                        str += mbar.createdate.ToString() + "','" + DateTime.Now.ToString() + "','" + mins.Operator + "'," + instorageid.ToString() + "," + stroid + ",'" + mbar.barbelong + "','" + mbar.bargraphic + "','";
                        str += mbar.barbelong + "','" + mbar.bargraphic + "'," + mbar.nwt.ToString() + ",0)";


                        sqlList.Add(str);   //插入库存数据sql

                        //更新barcode状态
                        sqlList.Add("update barcode set status=" + barstatus + " where id=" + mbar.id.ToString());

                        stroid = "0";
                    }
                }
            }
            if (instype == "0")
            {

                if (mins.outid == 0 && mins.cjoid > 0)
                {
                    //cjo授权处理
                    Model.VehicleRequire mvr = new VehicleRequire().GetModelByCjo((int)mcjo.id);
                    vload = mcjo.loadnum;
                    if (mvr.peculiarrequire.IndexOf("正常处理") >= 0)
                    {
                        sqlList.Add("update Cjo set status=4 where id=" + mcjo.id.ToString());
                    }
                    else
                    {
                        sqlList.Add("update Cjo set status=3 where id=" + mcjo.id.ToString());
                    }

                    sqlList.Add("update ltr_CJO set nwt=" + instotal.ToString() + ",wrldate='" + mins.instoragetime.ToString() + "',wrlid=" + mins.id.ToString() + ",wrlno='" + mins.wrlno + "',cjostatus='待处理' where cjoid=" + mins.cjoid.ToString());
                    if (mcjo.islocal == true)
                    {
                        Model.VehicleArrange mva = new VehicleArrange().GetModelByCjoid((int)mcjo.id);
                        if (mva != null)
                        {
                            sqlList.Add("update Vehiclor set currentstatus = currentstatus-1 where id in (" + mva.vehiderid.Substring(0, mva.vehiderid.Length - 1) + ")");
                            sqlList.Add("update PlateNumber set currentstatus = currentstatus-1 where id in (" + mva.platenumberid.Substring(0, mva.platenumberid.Length - 1) + ")");
                        }
                    }
                    totalnwt = instotal;
                    string updatesql = string.Format(@"update LoadNum set totalWeight=(select  ISNULL({0},0) + isnull((select isnull(SUM(instotal),0)  - Sum(isnull(mechanerr,0)) totalweight
from InStorage where loadnumid={1} and outid =0	and LEFT(wrlno,1) ='M' and id <> {2} group by loadnumid),0))
where id={1}", instotal, mcjo.loadnumid.ToString(), mins.id);
                    sqlList.Add(updatesql);
                    sqlList.Add("update ltr_CJO set nwt= " + totalnwt.ToString() + ",cjostatus='处理中' where cjoid=" + mcjo.id.ToString());
                }
                else
                {
                    totalnwt = instotal + (int)mins.mechanerr;
                    if (mouts != null)
                    {
                        vload = mouts.VLoadNum;
                        outs_status = (int)mouts.status;
                        if (totalnwt == mouts.totalWeight)
                            outs_status = 0;

                    }

                }
                UpdateFlStatus(mins.vloadnum);
            }
            else
            {
                if (mouts != null)
                {
                    outs_status = (int)mouts.status;
                }
                else
                {
                    outs_status = -1;
                }
                if (instype == "2")
                {
                    sqlList.Add("update LoadNum set finishWeight=" + instotal.ToString() + " where loadnum='" + mins.vloadnum + "';");
                    if (mins.vloadnum.Substring(0, 2) == "86")
                    {
                        decimal? lwt = new LoadNum().GetLoadID(mins.vloadnum).totalWeight;
                        if (mouts != null)
                        {
                           //同步分类入库状态
                            UpdateFlStatus(mins.vloadnum);
                            //if(dt.Rows[0][0].ToString()=="0")

                        }
                        else
                        {
                            if (mouts.totalWeight == instotal)
                            {
                                outs_status = 0;
                            }
                        }
                    }
                    else
                    {
                        decimal t5 = DbHelperSQL.GetNumSum("nwt", "barcode", "loadnumv='" + mouts.VLoadNum + "' and SUBSTRING(barcodevalue,10,1)='M'");
                        if (t5 > 0 && t5 == instotal)
                        {
                            outs_status = 0;
                        }
                    }
                }
                else if (instype == "3")         //生产入库完成 规则
                {
                    decimal insnwt = 0;
                    decimal inssh = 0;
                    dt = DbHelperSQL.Query("select isnull(sum(nwt),0) from ins_fuliao where instroageid=" + mins.id.ToString()).Tables[0];
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        insnwt = decimal.Parse(dt.Rows[0][0].ToString());
                    }
                    dt = DbHelperSQL.Query("select isnull(sum(nwt),0) from ins_sunhao where instroageid=" + mins.id.ToString()).Tables[0];
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        inssh = decimal.Parse(dt.Rows[0][0].ToString());
                    }

                    if (totalnwt + insnwt == mouts.totalWeight + inssh)
                    {
                        outs_status = 0;
                    }
                }
                else
                {
                    totalnwt = instotal;
                    if (mouts != null)
                    {
                        if (totalnwt == mouts.totalWeight)
                            outs_status = 0;
                    }
                }
                if (mouts != null)
                {
                    if(instype!="2")
                        sqlList.Add("update OutStorage set status=" + outs_status.ToString() + ",currentWeight=" + +instotal + " where id=" + mouts.id.ToString());
                    else
                        sqlList.Add("update OutStorage set currentWeight=" + +instotal + " where id=" + mouts.id.ToString());
                }

            }

            //2015年5月27日 20:51:21 直销入库重量修改
            if (instype == "7")
            {
                sqlList.Add("update LoadNum set totalWeight=" + instotal.ToString() + " where id=" + mcjo.loadnumid + "");
                sqlList.Add("update InStorage set status=0 where id=" + mins.id.ToString());
            }




            //2015年5月21日 增加如果传入month为空则clearingmonth补修改
            string strclearmonth = "";
            if (!string.IsNullOrEmpty(month))
                strclearmonth = "clearingmonth= '" + month + "' ,";

            sqlList.Add("update InStorage set  " + strclearmonth + " instotal=(select SUM(nwt) from barcode where InStrorageID=" + instorageid.ToString() + ") where id=" + instorageid.ToString());
            if(mouts != null)
                sqlList.Add("update OutStorage set status=0 where outStorageType='2' and currentWeight=totalWeight and id=" + mouts.id.ToString() );
    
            string strsql = sqlList.ToString();
            //sqlList = strSql.ToString().Split(';');
            try
            {

                DbHelperSQL.ExecuteSqlTran(sqlList);
            }
            catch (Exception ex)
            {
                log.Error(strsql + "异常：" + ex.Message);
                instorageid = 0;
            }

            return instorageid;

        }

        /// <summary>
        /// 入库提交
        /// </summary>
        /// <param name="instorageid">入库ID</param>
        /// <param name="instype">--1拆解入库 2分类入库  3生产入库 5是移库入库 0 称重入库</param>
        /// <returns></returns>
        public int InstorageSubmit(int instorageid, string instype, string month, string remark, string man)
        {

            DataTable dt = new DataTable();
            StringBuilder t = new StringBuilder(); //查询用的sql
            List<String> sqlList = new List<string>(); ; //事务用的sql
            t.Append("select * from barcode where InStrorageID=" + instorageid.ToString());
            dt = DbHelperSQL.Query(t.ToString()).Tables[0];
            decimal instotal = 0;
            decimal totalnwt = 0;
            string InDepart = "";
            string OutDepart = "";
            Model.InStorage mins = new InStorage().GetModel(instorageid);
            string vload = "";
            if (mins == null)
                return -1;
            Model.Cjo mcjo = null;
            Model.OutStorage mouts = null;
            int outs_status = 0;

            string str = "";

            if (mins.outid == 0 && mins.cjoid > 0)
            {
                //cjo授权处理
                mcjo = new Cjo().GetModel((int)mins.cjoid);
                InDepart = mcjo.belogcity;
                OutDepart = mcjo.graphiccity;
                log.Info("cjo: " + mcjo.id.ToString());
            }
            else
            {
                mouts = new OutStorage().GetModel((int)mins.outid);
                log.Info("outid: " + mouts.id.ToString());
            }

            string stroid = "0";
            int barstatus = 0;
            switch (instype)
            {
                case "2":       //分类入库
                    barstatus = 3;
                    break;
                case "7":       //直销入库
                    barstatus = 13;
                    break;
                case "5":       //移库入库
                    barstatus = 9;
                    break;
                case "3":       //生产入库
                    barstatus = 7;
                    break;
                case "1":       //拆解入库  6盘库入库另
                    barstatus = 5;
                    break;
                case "0":       //称重入库
                    barstatus = 1;
                    break;
                case "6":
                    barstatus = 11;
                    break;      //盘库入库
                default:
                    break;

            }

            if (dt != null && dt.Rows.Count > 0)
            {
                Model.BarCode mbar = new Model.BarCode();
                BarCode dbar = new BarCode();
                log.Info("datatable rows:" + dt.Rows.Count.ToString());
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    mbar = dbar.DataRowToModel(dt.Rows[i]);
                    instotal += (decimal)mbar.nwt;              //入库单重量
                    if (mbar.status == -1)
                    {
                        //插入库存表数据
                        log.Info("barcodeid:" + mbar.id.ToString());
                        if (mbar.OutStrorageID != null)
                            stroid = mbar.OutStrorageID.ToString();
                        else
                            stroid = "0";
                        str = " insert into StorageData(barcodeid,storageid,isused,startNwt,endNwt,nwt,storageno,InOrOut,goodsid,goodscode,goodsName,inoutid,bardate,operDate,operman,instorageid,outstoageid,belongcity,graphiccity,src_bcity,src_gcity,insnwt,outsnwt)";
                        str += " values(" + mbar.id.ToString() + "," + mbar.storageid.ToString() + "," + "0,0,0,";//期初期末值另表，默认为0
                        str += mbar.nwt.ToString() + ",'" + mbar.stroageNo + "',1," + mbar.goodsid.ToString() + ",'" + mbar.goodscode + "','" + mbar.goodsname.Replace("'", "\"") + "'," + mbar.InStrorageID.ToString() + ",'";
                        str += mbar.createdate.ToString() + "','" + DateTime.Now.ToString() + "','" + mins.Operator + "'," + instorageid.ToString() + "," + stroid + ",'" + mbar.barbelong + "','" + mbar.bargraphic + "','";
                        str += mbar.barbelong + "','" + mbar.bargraphic + "'," + mbar.nwt.ToString() + ",0)";


                        sqlList.Add(str);   //插入库存数据sql

                        //更新barcode状态
                        //判断是否已经出过库
                        BarCode bar = new BarCode();
                        Model.BarCode tbar = bar.BarCodeExists(mbar.barcodevalue);
                        if (tbar != null && tbar.OutStrorageID != null )
                        { 
                            str = "";
                        str = " insert into StorageData(barcodeid,storageid,isused,startNwt,endNwt,nwt,storageno,InOrOut,goodsid,goodscode,goodsName,inoutid,bardate,operDate,operman,instorageid,outstoageid,belongcity,graphiccity,src_bcity,src_gcity,insnwt,outsnwt)";
                        str += " values(" + mbar.id.ToString() + "," + mbar.storageid.ToString() + "," + "0,0,0,";//期初期末值另表，默认为0
                        str += mbar.nwt.ToString() + ",'" + mbar.stroageNo + "',2," + mbar.goodsid.ToString() + ",'" + mbar.goodscode + "','" + mbar.goodsname.Replace("'", "\"") + "'," + mbar.InStrorageID.ToString() + ",'";
                        str += mbar.createdate.ToString() + "','" + DateTime.Now.ToString() + "','" + mins.Operator + "'," + tbar.OutStrorageID.ToString() + "," + stroid + ",'" + mbar.barbelong + "','" + mbar.bargraphic + "','";
                        str += mbar.barbelong + "','" + mbar.bargraphic + "'," + "0," +mbar.nwt.ToString() + ")";
                         sqlList.Add(str);//已经出过库的自动出库 
                            sqlList.Add("update barcode set status=" + tbar.status.ToString() + " where id=" + mbar.id.ToString());
                        }
                        else
                        {
                            sqlList.Add("update barcode set status=" + barstatus + " where id=" + mbar.id.ToString());
                        }

                        stroid = "0";
                    }
                }
            }
            if (instype == "0")
            {
                if (mins.outid == 0 && mins.cjoid > 0)
                {
                    //cjo授权处理
                    Model.VehicleRequire mvr = new VehicleRequire().GetModelByCjo((int)mcjo.id);
                    vload = mcjo.loadnum;
                    if (mvr.peculiarrequire.IndexOf("正常处理") >= 0)
                    {
                        sqlList.Add("update Cjo set status=4 where id=" + mcjo.id.ToString());
                    }
                    else
                    {
                        sqlList.Add("update Cjo set status=3 where id=" + mcjo.id.ToString());
                    }

                    sqlList.Add("update ltr_CJO set nwt=" + instotal.ToString() + ",wrldate='" + mins.instoragetime.ToString() + "',wrlid=" + mins.id.ToString() + ",wrlno='" + mins.wrlno + "',cjostatus='待处理' where cjoid=" + mins.cjoid.ToString());
                    if (mcjo.islocal == true)
                    {
                        Model.VehicleArrange mva = new VehicleArrange().GetModelByCjoid((int)mcjo.id);
                        if (mva != null)
                        {
                            sqlList.Add("update Vehiclor set currentstatus = currentstatus-1 where id in (" + mva.vehiderid.Substring(0, mva.vehiderid.Length - 1) + ")");
                            sqlList.Add("update PlateNumber set currentstatus = currentstatus-1 where id in (" + mva.platenumberid.Substring(0, mva.platenumberid.Length - 1) + ")");
                        }
                    }
                    totalnwt = instotal;
                    string updatesql = string.Format(@"update LoadNum set totalWeight=(select  ISNULL({0},0) + isnull((select isnull(SUM(instotal),0)  - Sum(isnull(mechanerr,0)) totalweight
from InStorage where loadnumid={1} and outid =0	and LEFT(wrlno,1) ='M' and id <> {2} group by loadnumid),0))
where id={1}", instotal, mcjo.loadnumid.ToString(), mins.id);
                    sqlList.Add(updatesql);
                    sqlList.Add("update ltr_CJO set nwt= " + totalnwt.ToString() + ",cjostatus='处理中' where cjoid=" + mcjo.id.ToString());
                }
                else
                {
                    totalnwt = instotal + (int)mins.mechanerr;
                    if (mouts != null)
                    {
                        vload = mouts.VLoadNum;
                        outs_status = (int)mouts.status;
                        if (totalnwt == mouts.totalWeight)
                            outs_status = 0;

                    }

                }
            }
            else
            {
                if (mouts != null)
                {
                    InDepart = mouts.belogarea;
                    OutDepart = mouts.graphicCity;
                    outs_status = (int)mouts.status;
                }
                else
                {
                    outs_status = -1;
                }
                if (instype == "2")
                {
                    sqlList.Add("update LoadNum set finishWeight=" + instotal.ToString() + " where loadnum='" + mins.vloadnum + "'");
                    if (mins.vloadnum.Substring(0, 2) == "86")
                    {
                        decimal? lwt = new LoadNum().GetLoadID(mins.vloadnum).totalWeight;
                        if (lwt == instotal)
                        {
                            outs_status = 0;
                        }
                    }
                    else
                    {
                        decimal t5 = DbHelperSQL.GetNumSum("nwt", "barcode", "loadnumv='" + mouts.VLoadNum + "' and SUBSTRING(barcodevalue,10,1)='M'");
                        if (t5 > 0 && t5 == instotal)
                        {
                            outs_status = 0;
                        }
                    }
                }
                else if (instype == "3")         //生产入库完成 规则
                {
                    decimal insnwt = 0;
                    decimal inssh = 0;
                    dt = DbHelperSQL.Query("select isnull(sum(nwt),0) from ins_fuliao where instroageid=" + mins.id.ToString()).Tables[0];
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        insnwt = decimal.Parse(dt.Rows[0][0].ToString());
                    }
                    dt = DbHelperSQL.Query("select isnull(sum(nwt),0) from ins_sunhao where instroageid=" + mins.id.ToString()).Tables[0];
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        inssh = decimal.Parse(dt.Rows[0][0].ToString());
                    }

                    if (totalnwt + insnwt == mouts.totalWeight + inssh)
                    {
                        outs_status = 0;
                    }
                }
                else
                {
                    totalnwt = instotal;
                    if (mouts != null)
                    {
                        if (totalnwt == mouts.totalWeight)
                            outs_status = 0;
                    }
                }
                if (mouts != null)
                {
                    sqlList.Add("update OutStorage set status=" + outs_status.ToString() + ",currentWeight=" + +instotal + " where id=" + mouts.id.ToString());
                }

            }

            //2015年5月27日 20:51:21 直销入库重量修改
            if (instype == "7")
            {
                sqlList.Add("update LoadNum set totalWeight=" + instotal.ToString() + " where id=" + mcjo.loadnumid + "");
            }




            //2015年5月21日 增加如果传入month为空则clearingmonth补修改
            string strclearmonth = "";
            if (!string.IsNullOrEmpty(month))
                strclearmonth = "clearingmonth= '" + month + "' ,";



            sqlList.Add("update InStorage set  " + strclearmonth + " remark='" + remark + "',instotal=(select SUM(nwt) from barcode where InStrorageID=" + instorageid.ToString() + "),OutDepart='" + OutDepart + "',InDepart='" + InDepart + "' where id=" + instorageid.ToString());

            object r = DbHelperSQL.GetSingle("select remark from InStorage where id=" + instorageid.ToString());
            if (r != (object)remark)
            {
                sqlList.Add("INSERT INTO ins_remark(insid,createtime,remark,operman) VALUES (" + instorageid.ToString() + ",getdate(),'" + remark + "','" + man + "')");
            }

            sqlList.Add("update barcode set status= " + barstatus + " where InStrorageID=" + instorageid.ToString() + " and status is null ");

            string strsql = sqlList.ToString();
            //sqlList = strSql.ToString().Split(';');
            try
            {

                DbHelperSQL.ExecuteSqlTran(sqlList);
            }
            catch (Exception ex)
            {
                log.Error(strsql + "异常：" + ex.Message);
                instorageid = 0;
            }
            return instorageid;

        }



        /// <summary>
        /// 入库查询
        /// </summary>
        /// <param name="LoadNum">Load号</param>
        /// <param name="cum">客户</param>
        /// <param name="datetime1">入库日期起</param>
        /// <param name="datetime2">入库日期止</param>
        /// <param name="intye">入库方式</param>
        /// <param name="un_goodscode">处理前物品编码</param>
        /// <param name="bar_goodscode">处理后物品编码</param>
        /// <param name="un_ldh">处理前联单号</param>
        /// <param name="wrlno">入库单号</param>
        /// <param name="storageno">库位</param>
        /// <param name="bar_value">条码</param>
        /// <param name="boxid">箱号</param>
        /// <param name="belongcity">归属地</param>
        /// <param name="graphiccity">物理地</param>
        /// <returns></returns>
        public DataTable getInStorageItem(string LoadNum, string cum, string datetime1, string datetime2,
                            string intye, string un_goodscode, string bar_goodscode,
                            string un_ldh, string wrlno, string storageno, string bar_value, string boxid, string belongcity,
                            string graphiccity)
        {
            DataTable dt = new DataTable();
            SqlParameter[] sqlparameter ={
                new SqlParameter("@LoadNum",SqlDbType.VarChar,20),
                new SqlParameter("@cum",SqlDbType.VarChar,100),
                new SqlParameter("@insDateStart",SqlDbType.DateTime),
                new SqlParameter("@insDateEnd",SqlDbType.DateTime),
               new SqlParameter("@InType",SqlDbType.VarChar,5),
                new SqlParameter("@goodsCodeun",SqlDbType.VarChar,25),
               new SqlParameter("@goodsCodebar",SqlDbType.VarChar,25),
                new SqlParameter("@ldhun",SqlDbType.VarChar,15),
               new SqlParameter("@wrlno",SqlDbType.VarChar,20),
                new SqlParameter("@storageno",SqlDbType.VarChar,20),
               new SqlParameter("@barvalue",SqlDbType.VarChar,35),
                new SqlParameter("@boxid",SqlDbType.VarChar,10),
                new SqlParameter("@BelongCity",SqlDbType.VarChar,15),
                new SqlParameter("@GraphicCity",SqlDbType.VarChar,15)
            
             };
            sqlparameter[0].Value = LoadNum;
            sqlparameter[1].Value = cum;
            if (datetime1.Trim() == "" || datetime2.Trim() == "")
            {
            }
            else
            {
                sqlparameter[2].Value = datetime1;
                sqlparameter[3].Value = datetime2;
            }
            sqlparameter[4].Value = intye;
            sqlparameter[5].Value = un_goodscode;
            sqlparameter[6].Value = bar_goodscode;
            sqlparameter[7].Value = un_ldh;
            sqlparameter[8].Value = wrlno;
            sqlparameter[9].Value = storageno;
            sqlparameter[10].Value = bar_value;
            sqlparameter[11].Value = boxid;
            sqlparameter[12].Value = belongcity;
            sqlparameter[13].Value = graphiccity;

            SqlDataReader dr = DbHelperSQL.RunProcedure("EWMS_GETInstorageItems", sqlparameter);
            dt.Load(dr);
            dr.Close();
            return dt;
        }


        /// <summary>
        /// 获取入库单维护数据集
        /// </summary>
        /// <returns></returns>
        public DataTable GetInsMangeList(string bcity,string gcity, int currpage, int pagesize, out int totalpage, string serach)
        {
            DataTable dt = new DataTable();
            SqlParameter[] sqlparameter ={
                 new SqlParameter("@bcity",SqlDbType.VarChar,20)
                ,new SqlParameter("@gcity",SqlDbType.VarChar,20)
                ,new SqlParameter("@currpage",SqlDbType.Int)
                ,new SqlParameter("@Length",SqlDbType.Int)
                ,new SqlParameter("@totalcount",SqlDbType.Int)
                ,new SqlParameter("@serach",SqlDbType.VarChar,200)
             };
            sqlparameter[0].Value = bcity;
            sqlparameter[1].Value = gcity;
            sqlparameter[2].Value = currpage;
            sqlparameter[3].Value = pagesize;
            sqlparameter[4].Direction = ParameterDirection.Output;
            sqlparameter[5].Value = serach;
            dt = DbHelperSQL.RunProcedure("EWMS_InstoraegeMaintenanceList", sqlparameter, "tab").Tables[0];
            totalpage = int.Parse(sqlparameter[3].Value.ToString());
            return dt;
        }

        public DataTable GetTab(string bcity,string gcity, int currpage, int pagesize, out int totalpage, string serach)
        {
            DataTable dt = new DataTable();

            String strSql = "DECLARE @bcity varchar(20) = '" + bcity + "';";
            strSql += "DECLARE @gcity varchar(20) = '" + gcity + "';";
            strSql += "DECLARE @currpage int = " + currpage.ToString() + ";";
            strSql += "DECLARE @Length int = " + pagesize.ToString() + ";";
            strSql += "DECLARE @serach varchar(150) = '" + serach + "';";
            strSql += " DECLARE @totalcount int=0;";
            strSql += @"
        
            
            DECLARE @start int
	        if @currpage is null 
		        set @start=0 
	        ELSE 
		        set @start=@currpage
	        if @serach is null set @serach=''
	        if @gcity is null set @gcity=''
	        if @bcity is null set @bcity=''

            Create table #t1(
		        rowno int,
		        id int NULL,
		        cjoid int NULL,
                cjono varchar(20) NULL,
		        wrlno varchar(30) NULL,
                bcity varchar(30) NULL,
                gcity varchar(30) NULL,
		        outid int NULL,
		        outno varchar(30) NULL,
		        status int NULL,
		        instoragetime varchar(30) NULL,
		        loadnumv varchar(30) NULL,
		        totalnwt decimal NULL,
		        cumname varchar(200) NULL,
		        rdlcurl varchar(300) NULL,
		        updateurl varchar(300) NULL
	        )
	        ;
	        with o_move as		--出库
	        (
		        -- 移库
		        select outs.id outid, v.inscumname cumname,VLoadNum,outs.outStorageType,outs.outStorageNo,outs.status ostatus,InDepart bcity,OutDepart gcity
			        from OutStorage outs,Vload v
			        where v.outid=outs.id
				        and Left(InDepart,LEN(@bcity))=@bcity
                        and Left(OutDepart,LEN(@gcity))=@gcity
				        --and outs.outStorageType <> '3'
				        --and ( outs.outStorageType <> '31'
				        --      or (outs.outStorageType='31' and outs.status=14)
				        --    )
	
		        union
		        select	outs.id outid, l.cumname cumname,VLoadNum,outs.outStorageType,outs.outStorageNo,outs.status,InDepart,OutDepart
			        from OutStorage outs,LoadNum l
			        where  outs.VLoadNum=l.loadnum
				        and Left(InDepart,LEN(@bcity))=@bcity
                        and Left(OutDepart,LEN(@gcity))=@gcity
				        --and outs.outStorageType <> '3'
				        --and ( outs.outStorageType <> '31'
				        --      or (outs.outStorageType='31' and outs.status=14))
	        ),
	        t_ins as
	        (
			        select 
				        ROW_NUMBER() OVER(order by ins.id desc) rowno,
				        ins.id,
				        ins.cjoid,
                        c.cjono,
				        ins.wrlno,
				        ins.outid,
				        o_move.outStorageNo outno,
				        ins.status,
				        Cast(YEAR(instoragetime) as varchar(4))
							        +'-' + RIGHT('00'+CAST(MONTH(instoragetime) as varchar(2)),2)
							        +'-' + RIGHT('00'+CAST(DAY(instoragetime) as varchar(2)),2) instoragetime,
				        ins.vloadnum loadnumv,
				        isnull(ins.instotal,0) totalnwt,
				        case LEFT(ins.vloadnum,2)
					        When '86' Then l.cumname
					        ELSE o_move.cumname END cumname,
				        case LEFT(ins.wrlno,2) 
					        When 'M0' Then '/RDLC/WRLInfoRDLC.aspx?wrlid='+CONVERT(varchar(10),ins.id) 
					        When 'RS' Then '/Production/SelectReport.aspx?insid='+CONVERT(varchar(10),ins.id) 
					        ELSE '/RDLC/SegregationRDLC.aspx?instorageID='+CONVERT(varchar(10),ins.id) 
					        END rdlcurl,
				        case o_move.outStorageType
					        When '31' THEN '/Production/ProduceAudit.aspx?insid=' + CONVERT(varchar(10),ins.id)
					        ELSE
						        case LEFT(ins.wrlno,2) 
							        When 'RZ' Then '/storage/InStorage.aspx?insid='+CONVERT(varchar(10),ins.id) 
							        WHEN 'M0' THEN  '/storage/Inwrl.aspx?insid='+CONVERT(varchar(10),ins.id) 
							        ELSE '/storage/InStorage.aspx?insid='+CONVERT(varchar(10),ins.id) 
						        END 
					        END updateurl,
                        ins.InDepart bcity,
                        ins.OutDepart gcity
			        from InStorage ins 
				        left join o_move on ins.outid=o_move.outid
				        left join LoadNum l on ins.vloadnum=l.loadnum
                        left join cjo c on ins.cjoid=c.id
			        where LEFT(ins.wrlno,2)<>'RP' and LEFT(ins.wrlno,2)<>'PR' 
				        and LEFT(ins.wrlno,2)<>'RB'
				        and (LEFT(ins.wrlno,2)<>'RS' or (LEFT(ins.wrlno,2)='RS' and ostatus=0))
				        and Left(InDepart,LEN(@bcity))=@bcity
                        and Left(OutDepart,LEN(@gcity))=@gcity
				        and (@serach='' or 
					        CHARINDEX(@serach,(wrlno+isnull(l.loadnum,'')
						        +Cast(ISNULL(instotal,0) as varchar(20))
						        +isnull(outno,'')+isnull(l.cumname,'') 
						        + Cast(YEAR(instoragetime) as varchar(4))
						        +isnull(ins.vloadnum,'')
						        +'-' + RIGHT('00'+CAST(MONTH(instoragetime) as varchar(2)),2)
						        +'-' + RIGHT('00'+CAST(DAY(instoragetime) as varchar(2)),2)
						        ))>0
						        --or (ISNUMERIC(@serach)=1 and totalWeight=Cast(@serach as decimal(10,2)))
						        )
	        )	
	
	        insert into #t1(rowno,
		        id,
		        cjoid,
                cjono,
		        wrlno,
		        outid,
		        outno,
		        status,
		        instoragetime,
		        loadnumv,
		        totalnwt,
		        rdlcurl,
		        updateurl,
		        cumname,
                bcity,
                gcity)
		        select rowno,
				        id,
				        cjoid,
                        cjono,
				        wrlno,
				        outid,
				        outno,
				        status,
				        instoragetime,
				        loadnumv,
				        totalnwt, 
				        rdlcurl,
				        updateurl,
				        cumname,
                        bcity,
                        gcity
			        from t_ins
			";
            string StrSql1 = strSql + @"
	        select * from #t1
		        where @currpage is null or @Length is null or (rowno between @start and  @start+ @Length-1)
	        drop table #t1";
            string StrSql2 = strSql + @"
	        set @totalcount=(select COUNT(1) from #t1)
	        drop table #t1
            select isnull(@totalcount,0)";
            dt = DbHelperSQL.Query(StrSql1).Tables[0];
            totalpage = int.Parse(DbHelperSQL.Query(StrSql2).Tables[0].Rows[0][0].ToString());
            return dt;
        }

        /// <summary>
        /// 更新备注
        /// </summary>
        /// <param name="uname"></param>
        /// <param name="remark"></param>
        /// <param name="insid"></param>
        /// <returns></returns>
        public string UpdateRemark(string uname, string remark, string insid)
        {
            string flag = "TRUE";
            string sql = "update InStorage set  remark='" + remark + "' where id=" + insid;
            try
            {
                object r = DbHelperSQL.GetSingle(sql);
                if (r != (object)remark)
                {
                    DbHelperSQL.GetSingle("INSERT INTO ins_remark(insid,createtime,remark,operman) VALUES (" + insid + ",getdate(),'" + remark + "','" + uname + "')");
                }
            }
            catch (Exception ex)
            {
                //Common.MessageBox.Show(ex.Message);
                flag = ex.Message;
            }
            return flag;
        }

        private int UpdateFlStatus(string loadnum)
        {
            int outs = 0;
            DataTable dt_0 = new DataTable();
            SqlParameter[] sp1 ={
                                                    new SqlParameter("@loadnum",SqlDbType.VarChar,20)
                            };
            sp1[0].Value = loadnum;

            DataSet tds = DbHelperSQL.RunProcedure("EWMS_InstorageFl", sp1, "t");
            if (tds.Tables[0] != null && tds.Tables[0].Rows.Count > 0 && tds.Tables[0].Rows[0][0] != null)
                outs = int.Parse(tds.Tables[0].Rows[0][0].ToString());
            return outs;
        }
        #endregion  ExtensionMethod
    }



}

